<?php


namespace app\index\controller;

use think\Db;
use think\db\Query;
use think\exception\DbException;

/**
 * 数据库高级语法
 * Class DbAdvancedTest
 * @package app\index\controller
 */
class DbAdvancedTest
{

    public function query_test()
    {
        try {
            $query = new Query();
            $query->where('age', '>=', 40);
            $res = Db::table('t_user')
                ->where($query)
                ->order(['age' => 'desc', 'name'])
                ->select();
            return json(['msg' => '查询成功！', 'data' => $res]);
        } catch (DbException $e) {
            return json(['msg' => '查询失败！', 'data' => $e]);
        }
    }

    /**
     * 动态查询
     * sql：SELECT * FROM `t_user` WHERE  `age` = 40
     */
    public function dynamic_query_test()
    {
        try {
            $res = Db::table('t_user')
                ->whereAge(40)
                ->select();
            return json(['msg' => '查询成功！', 'data' => $res]);
        } catch (\Exception $e) {
            return json(['msg' => '查询失败！', 'data' => $e]);
        }
    }

    /**
     * 动态查询
     * sql：SELECT * FROM `t_user` WHERE  `age` = 40 LIMIT 1
     */
    public function dynamic_get_query_test()
    {
        try {
            $res = Db::table('t_user')
                ->getByAge(40);
            return json(['msg' => '查询成功！', 'data' => $res]);
        } catch (\Exception $e) {
            return json(['msg' => '查询失败！', 'data' => $e]);
        }
    }

    /**
     * 条件查询
     * sql：
     */
    public function query_when_test()
    {
        try {
            $res = Db::table('t_user')
                ->when('id>0', function ($query) {
                    $query->where('age', '>', 40);
                });
            return json(['msg' => '查询成功！', 'data' => $res]);
        } catch (\Exception $e) {
            return json(['msg' => '查询失败！', 'data' => $e]);
        }
    }

    /**
     * 条件查询
     * sql：
     */
    public function query_event_test()
    {
        try {
            $res = Db::table('t_user')
                ->where('age', '>', 40)
                ->select();
            return json(['msg' => '查询成功！', 'data' => $res]);
        } catch (\Exception $e) {
            return json(['msg' => '查询失败！', 'data' => $e]);
        }
    }
}